package queryfish.db.db4o;

import java.io.File;
import java.io.IOException;

import javax.swing.JOptionPane;

import queryfish.antlr.parser.ParserQues;
import queryfish.antlr.parser.analysisQues;
import queryfish.google.search.CheckConnection;
import queryfish.google.search.GoogleSearch;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.read.biff.BiffException;

import com.db4o.Db4oEmbedded;
import com.db4o.ObjectContainer;

public class Connection {
	// fish_name
	public static String fish_name;
	// image
	public static String image;
	// something here
	// general information
	public static String science_name;
	public static String fish_set;
	public static String fish_class;
	public static String kind_of_fish;
	public static String same_name;
	public static String english_name;
	public static String vietnamese_name;
	public static String origination;
	// Biology
	public static String distribution;
	public static String fish_long;
	public static String water_temparature;
	public static String dH;
	public static String pH;
	public static String feeding;
	public static String reproduction;
	public static String water_field;
	// culture technology
	public static String aquarium;
	public static String feed_form;
	public static String moss_request;
	public static String light_request;
	public static String water_filter_request;
	public static String aeration_request;
	public static String kind_of_food;
	public static String caring;
	// description
	public static String description;
	public static String desLink = "";
	public static String strTitle1 = "";
	public static String strURL1 = "";
	public static String strTitle2 = "";
	public static String strURL2 = "";
	public static String strTitle3 = "";
	public static String strURL3 = "";
	public static String strTitle4 = "";
	public static String strURL4 = "";
	public static String many = "";
	//test ANTRL
	public static boolean test = true;

	public static void loadData(String question) {
		// kết nối với database
		// thực hiện truy vấn
		// thực hiện trả kết quả về
//		 new File("WaterFish").delete();
//		 accessDb4o();
//		 new File("WaterFish").delete();
		ObjectContainer db = Db4oEmbedded.openFile(
				Db4oEmbedded.newConfiguration(), "WaterFish");
		try {
//			 readData(db);
			ParserQues parser = new ParserQues();
			parser.methodParser(question);
			parser.testANTRL(question);
			if (parser.test == false) {
				analysisQues ana = new analysisQues();
				ana.takeKey(parser.question);
				CheckConnection.isInternetReachable();
				// kiểm tra kết nối internet
				// nếu có kết nối sẽ trả về link thông tin về cá, ngược lại
				if (CheckConnection.flagConnection == true) {
					GoogleSearch.getSearch(analysisQues.nameFish);
					desLink = "Xem thêm tại :";
					strTitle1 = GoogleSearch.arrTitle.get(0).toString();
					strURL1 = GoogleSearch.arrURL.get(0).toString();
					strTitle2 = GoogleSearch.arrTitle.get(1).toString();
					strURL2 = GoogleSearch.arrURL.get(1).toString();
					strTitle3 = GoogleSearch.arrTitle.get(2).toString();
					strURL3 = GoogleSearch.arrURL.get(2).toString();
					strTitle4 = GoogleSearch.arrTitle.get(3).toString();
					strURL4 = GoogleSearch.arrURL.get(3).toString();
				}
				// gọi hàm truy vấn
//				System.out.print(ana.nameFish + ana.keyworld);
				
				Query.queryFish(db, analysisQues.nameFish,
						analysisQues.keyworld);
//				System.out.print(Query.flag);
				// kiểm tra nếu nhiều hơn 1 loại cá
				if (Query.arrFishname.size() > 1) {
					String strFish = " ";
					for (int i = 0; i < Query.arrFishname.size(); i++) {
						strFish = strFish + Query.arrFishname.get(i) + ",";
//						System.out.print(Query.arrFishname.get(i)+"\n");
					}
					many = "bao gồm : " + strFish;
//					System.out.print(Query.arrFishname.size());
				}
				// gọi hàm sinh trả lời
				Answer.getAnswer();
			}
			else test = false;

		} catch (Exception e) {
			JOptionPane.showMessageDialog(null, "Không Tải Được Dữ Liệu !",
					"Error", JOptionPane.ERROR_MESSAGE);
		} finally {
			db.close();
		}
	}

	public static void accessDb4o() {
		ObjectContainer db = Db4oEmbedded.openFile(
				Db4oEmbedded.newConfiguration(), "WaterFish");
		try {
			// do something with db4o
		} finally {
			db.close();
		}
	}

	// important
	public static void readData(ObjectContainer db) {
		// quan trọng, không được xóa
		// đọc dữ liệu từ file excel vào database
		// sử dụng 1 lần
		// không được gọi lại
		// chỉ được gọi khi có thay đổi về dữ liệu
		// đọc theo từng dòng + cột

		File fileExcel = new File("Resource/fishdata.xls");
		try {
			WorkbookSettings ws = new WorkbookSettings();
			ws.setEncoding("CP1258");
			Workbook wb = Workbook.getWorkbook(fileExcel, ws);
			Sheet sheet = wb.getSheet(0);
			int rows = sheet.getRows();
			for (int row = 2; row < rows; row++) {

				// do something here
				// general information
				fish_name = sheet.getCell(0, row).getContents();
				science_name = sheet.getCell(1, row).getContents();
				fish_set = sheet.getCell(2, row).getContents();
				fish_class = sheet.getCell(3, row).getContents();
				kind_of_fish = sheet.getCell(4, row).getContents();
				same_name = sheet.getCell(5, row).getContents();
				english_name = sheet.getCell(6, row).getContents();
				vietnamese_name = sheet.getCell(7, row).getContents();
				origination = sheet.getCell(8, row).getContents();
				// biology
				distribution = sheet.getCell(9, row).getContents();
				fish_long = sheet.getCell(10, row).getContents();
				water_temparature = sheet.getCell(11, row).getContents();
				dH = sheet.getCell(12, row).getContents();
				pH = sheet.getCell(13, row).getContents();
				feeding = sheet.getCell(14, row).getContents();
				reproduction = sheet.getCell(15, row).getContents();
				water_field = sheet.getCell(16, row).getContents();
				// culture technology
				aquarium = sheet.getCell(17, row).getContents();
				feed_form = sheet.getCell(18, row).getContents();
				moss_request = sheet.getCell(19, row).getContents();
				light_request = sheet.getCell(20, row).getContents();
				water_filter_request = sheet.getCell(21, row).getContents();
				aeration_request = sheet.getCell(22, row).getContents();
				kind_of_food = sheet.getCell(23, row).getContents();
				caring = sheet.getCell(24, row).getContents();
				// image number
				image = sheet.getCell(25, row).getContents();
				description = sheet.getCell(26, row).getContents();
				//
				storeFish(db);

			}
		} catch (BiffException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			JOptionPane.showMessageDialog(null,
					"Không Tìm Thấy File Dữ Liệu !", "Error",
					JOptionPane.ERROR_MESSAGE);
		} catch (Exception e) {
			JOptionPane.showMessageDialog(null, "Không Thể Đọc File Dữ Liệu !",
					"Error", JOptionPane.ERROR_MESSAGE);
		}

	}

	// important
	public static void storeFish(ObjectContainer db) {
		// quan trọn không được xóa
		// lưu dữ liệu từ file excel vào database
		// chỉ được dùng khi có thay đổi dữ liệu
		try {
			WaterFish fish = new WaterFish(fish_name);
			Biology bio = new Biology(distribution, fish_long,
					water_temparature, dH, pH, feeding, reproduction,
					water_field);
			Culture_Tech tech = new Culture_Tech(aquarium, feed_form,
					moss_request, light_request, water_filter_request,
					aeration_request, kind_of_food, caring);
			General_Info info = new General_Info(science_name, fish_set,
					fish_class, kind_of_fish, same_name, english_name,
					vietnamese_name, origination);
			fish.setBiology(bio);
			fish.setCulture_technology(tech);
			fish.setGeneral_information(info);
			fish.setImage(image);
			fish.setDescription(description);
			db.store(fish);//lưu dự liệu lên db4o
		} catch (Exception e) {
			JOptionPane.showMessageDialog(null, "Không Thể Lưu Dữ Liệu !",
					"Error", JOptionPane.ERROR_MESSAGE);
		}
	}

}
